SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Prop3Q13] AS
BEGIN
   DECLARE @dbname  VARCHAR(50)
 DECLARE @FundID  VARCHAR(10)
 DECLARE @NAVDate DATETIME
 
 
 
 SET @FundID  = 'FSIC'
 SET @NAVDate = '9/30/2013'

 													
 SELECT   
		  
		  
		   i.InstrumentId, fci.FundCreditTransType,  iss.IssuerName, i.InstrName, id.InstrClass, p.PortfolioName, t.TradeType, t.TradeAmount, t.TradePrice, t.TradeDate,  prevDate.GAAPYldPurchPrc AS 'TD-1GAAPYieldAtPurch', 
 
  CASE WHEN a.PropReason IS NOT NULL THEN 1 ELSE 0 END AS IsProprietary ,
         a.PropReason ,
         a.PropTrack,
		 fci.TotalCredit,
		 iss.TTMEBITDA,
		 i.MaturityDate
		 
		 , prevDate.AmortCost 'AmortizedCostBeforeTrade',
		
		 t.AmortCost 'AmortCostTrade', a.IsActiveEndOfDay,
		 
		 CASE when fci.TotalCredit IS NOT NULL THEN 1 ELSE 0 END AS IsUnfunded, 
			fci.AvailCredit,a.GAAPYldPurchPrc, a.AmortCost, prevDate.AmortCostPctPar 'TD-1AmortCostPctPar'
 
 FROM       FSFundModel_Valcomm.Reporting.Asset a
            INNER JOIN FSFundModel_Valcomm.Reporting.Instrument i
            ON         i.ExportId     = a.ExportId
            AND        a.InstrumentId = i.InstrumentId
            
            INNER JOIN FSFundModel_Valcomm.Reporting.Issuer iss
            ON         iss.ExportId = a.ExportId
            AND        i.IssuerId   = iss.IssuerId
            
            INNER JOIN FSFundModel_Valcomm.Reporting.InstrumentDefinition id
            ON         i.InstDefId = id.InstDefId
            AND        id.ExportId = a.ExportId
            
            INNER JOIN FSFundModel_Valcomm.Reporting.FundIssuer fi
            ON         a.ExportId  = fi.ExportId
            AND        fi.IssuerId = iss.IssuerId
            
            INNER JOIN FSFundModel_Valcomm.Reporting.Portfolio p
            ON         a.ExportId    = p.ExportId
            AND        a.PortfolioId = p.PortfolioId
            
            INNER JOIN FSFundModel_Valcomm.Reporting.Fund f
            ON         a.ExportId = f.ExportId
			
            
            INNER JOIN FSFundModel_Valcomm.Reporting.Trade t
            ON         a.ExportId     = t.ExportId
            AND        a.InstrumentId = t.InstrumentId
            AND        a.LotId        = t.LotId
            AND        a.PortfolioId  = t.PortfolioId
            
			OUTER APPLY(

					SELECT   fi.*, 			        ft.FundCreditTransType ,
					        ft.TransAmt ,
					        ft.TransDate 
					        
					                FROM        
					FSFundModel_VALCOMM.Reporting.FundCreditTrans ft      
					INNER JOIN FSFundModel_Valcomm.Reporting.FundCreditInstrument fi ON 
								a.ExportId              = fi.ExportId 
					AND        a.InstrumentId           = fi.InstrumentId
				

					WHERE ft.ExportId = a.ExportId
					AND a.FundCreditInstrumentId = ft.FundCreditInstrumentId
					
			)  fci

			OUTER  APPLY ( 
				SELECT pd.*  FROM Valcomm.getAssetByNAVDateOffset(t.TradeDate, -1, t.PortfolioId,t.InstrumentId,t.LotId) pd --(f.PrevNAVDate, @FundID) pd
				 ) prevDate
 WHERE      a.ExportId                =
            (SELECT MAX(e.ExportId)
            FROM    FSFundModel_Valcomm.Reporting.Export e
            WHERE   e.FundId  = @FundID
            AND     e.NAVDate = @NAVDate)
AND t.TradeDate > '6/28/2013'


ORDER BY  id.SortOrder, t.TradeDate ASC 






END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/SEC', 'SCHEMA', N'dbo', 'PROCEDURE', N'Prop3Q13', NULL, NULL
GO
